package jsyf.oa.dao.system;

import com.alibaba.dubbo.common.utils.StringUtils;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.text.DecimalFormat;
import java.util.Arrays;
import java.util.Calendar;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import jsyf.com.util.DateUtils;
import jsyf.oa.dao.report.newAppReport.NewAppReportForZSQDao;
import jsyf.oa.dao.wechat.service_company.TokenDao;
import jsyf.oa.dao.wreport.WReportCusVisitDao;
import jsyf.oa.entity.system.MessageEntity;
import jsyf.oa.entity.system.MessageReceiverEntity;
import jsyf.oa.entity.system.SignInEntity;
import jsyf.oa.entity.wreport.WReportCusVisitEntity;
import jsyf.oa.idao.BaseDao;
import jsyf.oa.ientity.IBaseEntity;
import jsyf.oa.service.report.reportCommon.ReportCommonMethodsAndConstant;

import static jsyf.oa.dao.wechat.service_company.TokenDao.MsgTemplate.FIRST_VISIT_MESSAGE_ERROR;
import static jsyf.oa.dao.wechat.service_company.TokenDao.MsgTemplate.FIRST_VISIT_MESSAGE_ERROR_WE;


/**
 * Created by WHH-PC on 2018/1/8.
 */
@Repository
public class SignInDao extends BaseDao {

    @Autowired
    private WReportCusVisitDao wReportCusVisitDao;

    @Autowired
    private ReportCommonMethodsAndConstant common;

    @Autowired
    private MessageDao messageDao;
    @Autowired
    private MessageReceiverDao messageReceiverDao;
    @Autowired
    private CheckDao checkDao;

    @Autowired
    private NewAppReportForZSQDao newAppReportForZSQDao;
    @Autowired
    private UserDao userDao;
    @Autowired
    private TokenDao tokenDao;


    public SignInDao() {
        super.iBaseEntity = new SignInEntity();
    }

    @Override
    public IBaseEntity convertJson2Entity(JSONObject json) {
        return JSON.toJavaObject(json, SignInEntity.class);
    }

    //修改时间
    public Integer updateTime(String id) {
        StringBuffer sb = new StringBuffer();
        sb.append(" UPDATE `t_sys_sign_in` SET SIGN_OUT_TIME= NULL,");
        sb.append(" SIGN_OUT_ADDRESS=NULL,SIGN_OUT_LATITUDE=NULL,");
        sb.append(" SIGN_OUT_LONGITUDE=NULL,SIGN_OUT_URL=NULL,");
        sb.append(" SIGN_OUT_DISTANCE=NULL,SIGN_OUT_SPEED=NULL,");
        sb.append(" SIGN_OUT_TIME_DIFF=NULL,SIGN_OUT_ALWAYS_DISTANCE=NULL");
        sb.append(" WHERE ID ='" + id + "'");
        return this.getJdbcTemplate().update(sb.toString());

    }

    /**
     * 获取某人某天是否签到、某个月的考勤汇总
     *
     * @return
     */
    public JSONObject getSignInByUserId(String userId, String roleId, String month, String nowDay) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT ");
        sb.append("(SELECT count(*) FROM t_sys_sign_in WHERE SIGN_DATE='" + nowDay + "' AND USER_ID IN ('" + userId + "')) as isSignIn,  ");
        sb.append("IFNULL((SELECT SHOULD_SIGNIN FROM t_sys_should_sign_in WHERE ROLE_ID='" + roleId + "' AND SHOULD_MONTH='" + month + "'),0) as shouldCount, ");
        sb.append("(SELECT COUNT(*) FROM t_sys_sign_in WHERE LEFT(SIGN_DATE,7)='" + month + "' AND USER_ID='" + userId + "') as signInCount, ");
        sb.append("(SELECT COUNT(*) FROM t_sys_leave WHERE LEFT(LEAVE_DATE,7)='" + month + "' AND USER_ID='" + userId + "') as leaveCount ");
        return queryForJsonList(sb.toString()).get(0);
    }

    /**
     * 根据大区经理departId获取此大区和此大区的小区域及负责人
     *
     * @return
     */
    public List<JSONObject> getRegionListByBigRegionDepartId(String departId) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT regionId,managerName,managerRoleName,managerId,managerJobState,regionName,REPLACE(bigRegionName, '大区', '') as bigRegionName FROM v_sys_region where managerDepartId='" + departId + "' and parentId=0 ");
        sb.append("AND managerRoleName in('大区经理')  AND  managerJobState<>1 ");
        sb.append("group by managerId  ");
        sb.append("UNION  ");
        sb.append("SELECT regionId,managerName,managerRoleName,managerId,managerJobState,regionName,REPLACE(bigRegionName, '大区', '') as bigRegionName FROM v_sys_region where managerDepartId='" + departId + "' and parentId!=0 ");
        sb.append("AND managerRoleName in('区域经理')  AND  managerJobState<>1 ");
        sb.append("group by managerId ");
        return queryForJsonList(sb.toString());
    }

    /**
     * 获取区域支持负责所有区域
     *
     * @return
     */
    public List<JSONObject> getSupportRegionList(String userId) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT a.*,REPLACE(b.`name`, '大区', '') as bigRegionName,c.username as userName,c.userid as userId,d.roleName,d.jobState from ( ");
        sb.append("SELECT a.REGION_ID as regionId,REPLACE(b.`name`, '大区', '') as regionName,b.parent_id as parentId from t_sys_user_2_region as a  ");
        sb.append("left join t_sys_region as b on a.REGION_ID = b.id ");
        sb.append("where a.user_id='" + userId + "'  ");
        sb.append("order by b.parent_id ASC,b.sort_no asc) as a ");
        sb.append("LEFT JOIN t_sys_region as b on a.parentId=b.id ");
        sb.append("LEFT JOIN v_region_manager as c on a.regionId=c.regionid ");
        sb.append("LEFT JOIN v_sys_user as d on c.userid=d.id  ");
        sb.append("order by parent_id asc ");
        return queryForJsonList(sb.toString());
    }

    /**
     * 获取部门考勤汇总（当天）
     *
     * @return
     */
    public JSONObject getSignInByDepart(String departId, String signDate) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT  \n");
        sb.append("(SELECT COUNT(*) FROM v_sys_user where departId='" + departId + "'  AND jobState != 1  AND id NOT IN (SELECT USER_ID FROM t_sys_sign_in_debar) ) AS shouldCount,  \n");
        sb.append("(SELECT COUNT(*) FROM t_sys_sign_in   ");
        sb.append("where user_id in (SELECT id FROM v_sys_user where departId='" + departId + "' AND departDelFlag=0  AND jobState != 1  AND id NOT IN (SELECT USER_ID FROM t_sys_sign_in_debar)) \n");
        sb.append("AND SIGN_DATE='" + signDate + "') as signInCount, ");
        sb.append("(SELECT COUNT(*) FROM t_sys_leave ");
        sb.append("where user_id in (SELECT id FROM v_sys_user where departId='" + departId + "' AND departDelFlag=0  AND jobState != 1 AND id NOT IN (SELECT USER_ID FROM t_sys_sign_in_debar)) \n");
        sb.append("AND LEAVE_DATE='" + signDate + "') as leaveCount ");
        return queryForJsonList(sb.toString()).get(0);
    }


    /**
     * 获取某个个公司所有销售部人员
     *
     * @return
     */
    public List<JSONObject> getUserListByCompany(String companyId) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT '' as sort_no, id as userId,name as userName,'' as regionName,roleName,departName,'' as parentId,  ");
        sb.append("'' as regionId from v_sys_user where departId in(  ");
        sb.append("SELECT id FROM t_sys_depart where COMPANY_ID='" + companyId + "' ");
        sb.append(") AND id NOT IN (SELECT USER_ID FROM t_sys_sign_in_debar) ");
        sb.append("UNION ");
        sb.append("SELECT a.sort_no,b.userid,b.username as userName,REPLACE(a.`name`, '大区', '') as regionName,c.roleName,'' as departName,a.parent_id as parentId,a.id as regionId from t_sys_region as a ");
        sb.append("left join v_region_manager as b on a.id=b.regionid ");
        sb.append("LEFT JOIN v_sys_user as c on b.userid=c.id  ");
        sb.append("where company_id='" + companyId + "'  AND c.id NOT IN (SELECT USER_ID FROM t_sys_sign_in_debar) ");
        sb.append("ORDER BY parentId asc, sort_no asc,username desc ");
        return queryForJsonList(sb.toString());
    }

    /**
     * 获取销售部的所有人员
     *
     * @return
     */
    public List<JSONObject> getSalerDepartUserList(String departId) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT id as userId,name as userName,'' as regionName,roleName,departName,'' as parentId,   ");
        sb.append("'' as regionId from v_sys_user where departId='" + departId + "' AND id NOT IN (SELECT USER_ID FROM t_sys_sign_in_debar)   ");
        sb.append("AND jobState!=1 ");
        return queryForJsonList(sb.toString());
    }

    /**
     * 获取个公司考勤汇总（当天）
     *
     * @return
     */
    public JSONObject getSignInByCompany(String companyId, String signDate) {
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT \n");
        sb.append("   ( \n");
        sb.append("    SELECT COUNT(*) FROM v_sys_user \n");
        sb.append("    WHERE companyId='" + companyId + "' \n");
        sb.append("      AND delFlag=0  AND id NOT IN (SELECT USER_ID FROM t_sys_sign_in_debar) \n");
        sb.append("    ) AS shouldCount, \n");
        sb.append("  ( \n");
        sb.append("  SELECT COUNT(*) FROM t_sys_sign_in \n");
        sb.append("  WHERE USER_ID in (SELECT id FROM v_sys_user \n");
        sb.append("  WHERE companyId='" + companyId + "' \n");
        sb.append("  AND delFlag=0 ) \n");
        sb.append("  AND SIGN_DATE='" + signDate + "' AND USER_ID NOT IN (SELECT USER_ID FROM t_sys_sign_in_debar)) as signInCount,\n");
        sb.append("  (SELECT COUNT(*) FROM t_sys_leave WHERE user_id in \n");
        sb.append("  (SELECT id FROM v_sys_user where companyId='" + companyId + "' AND delFlag=0 \n");
        sb.append("  ) AND LEAVE_DATE='" + signDate + "' AND USER_ID NOT IN (SELECT USER_ID FROM t_sys_sign_in_debar)) as leaveCount ");
        return queryForJsonList(sb.toString()).get(0);
    }

    /**
     * 获取个公司考勤汇总（某个月）
     *
     * @return
     */
    public JSONObject getSignInCompanyTotal(String companyId, String signInMonth) {
        String day = signInMonth + "-01";
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT  \n");
        sb.append(" (SELECT COUNT(*)*DAYOFMONTH(LAST_DAY('" + day + "')) FROM v_sys_user where companyId='" + companyId + "' AND delFlag=0 \n");
        sb.append(" ) AS shouldCount, \n");
        sb.append(" (SELECT COUNT(*) FROM t_sys_sign_in   where user_id in (SELECT id FROM v_sys_user \n");
        sb.append(" where companyId='" + companyId + "' AND delFlag=0) \n");
        sb.append(" AND LEFT(SIGN_DATE,7)='" + signInMonth + "') as signInCount, \n ");
        sb.append(" (SELECT COUNT(*) FROM t_sys_leave where user_id in \n");
        sb.append(" (SELECT id FROM v_sys_user where companyId='" + companyId + "' AND delFlag=0 \n");
        sb.append(" ) AND LEFT(LEAVE_DATE,7)='" + signInMonth + "') as leaveCount ");
        return queryForJsonList(sb.toString()).get(0);
    }

    /**
     * 获取登陆用户是否有下级
     *
     * @return
     */
    public JSONObject getUserParent(String userId) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT COUNT(1) as childrenCount FROM v_sys_user where  parentId='" + userId + "' AND delFlag=0 ");
        return queryForJsonList(sb.toString()).get(0);
    }

    public JSONObject saveFirstOrLastVisitDiff(String jsonData) {
        SignInEntity entity = JSON.toJavaObject(JSON.parseObject(jsonData), SignInEntity.class);
        JSONObject jsonObject = getSignInByDay(entity.getUserId(), entity.getSignDate());
        if (jsonObject != null) {
            //有签到
            entity = JSON.toJavaObject(jsonObject, SignInEntity.class);
            if (StringUtils.isEmpty(entity.getSignOutAddress())) {
                //签到与首次拜访
                List<JSONObject> list = wReportCusVisitDao.getJSONListByCondition("AND CREATE_USER_ID = ?\n" +
                        "AND VISIT_MODE = '1'\n" +
                        "AND REPORT_DATE = ?\n" +
                        "order by CREATE_TIME ASC\n", new String[]{entity.getCreateUserId(), entity.getSignDate()});
                if (list != null && list.size() > 0 && StringUtils.isEmpty(entity.getSignInTimeDiff())) {
                    WReportCusVisitEntity visitEntity = JSON.toJavaObject(list.get(0), WReportCusVisitEntity.class);
                    //时间差
                    long between = Math.abs(visitEntity.getArrivalsTime().getTime() - entity.getCreateTime().getTime());
                    String hour = new DecimalFormat("0.00").format(Double.parseDouble(String.valueOf(between)) / (60 * 60 * 1000));

                    String signInTimeDiff = DateUtils.calculateTimeDifference(visitEntity.getArrivalsTime(), entity.getCreateTime());
                    //距离差
                    String signInDistance = common.getDistanceByLatAndLng(
                            Double.parseDouble(visitEntity.getArrivalsLatitude()),
                            Double.parseDouble(visitEntity.getArrivalsLongitude()),
                            Double.parseDouble(entity.getLatitude()),
                            Double.parseDouble(entity.getLongitude()));

                    //平均速度
                    if (signInDistance.contains("米")) {
                        signInDistance = String.valueOf((Double.parseDouble(signInDistance.replace("米", "")) / 1000));
                    } else {
                        signInDistance = signInDistance.replace("公里", "").replace("千米", "");
                    }
                    String signInSpeed = new DecimalFormat("0.00").format(Double.parseDouble(signInDistance) / Double.parseDouble(hour));
                    if (between < 36000) {
                        //如果操作签到与面访签到间隔小于36s，时间被除数为0，速度为∞，修改为0
                        signInSpeed = "0";
                    }
                    entity.setSignInDistance(signInDistance + "公里"); //100公里
                    entity.setSignInSpeed(signInSpeed + "km/h"); //100Km/h
                    entity.setSignInTimeDiff(signInTimeDiff); // 1小时20分15秒
                    update(entity);

                    //时间/距离不合理插入消息
                    if (isInvalid(signInDistance, between)) {
                        JSONObject userJson = userDao.getViewJSONById(entity.getCreateUserId());
                        String name = userJson.getString("name");
                        //签到的首次拜访往消息表存数据
                        String allParentStr = checkDao.getParentByUserId(entity.getCreateUserId(), true);
                        allParentStr = "'" + entity.getCreateUserId() + "'," + allParentStr;//添加自己
                        String[] allParentArr = allParentStr.split(",");


                        //去重有兼职的上级
                        Set<String> set = new HashSet<>(Arrays.asList(allParentArr));
                        String noRepeatParentStr = set.toString().replace("[", "").replace("]", "");

                        StringBuffer condition = new StringBuffer();
                        condition.append(" and roleName in ('区域经理','区域组长','大区经理','区域支持','区域总监')\n");
                        List<JSONObject> userIdsList = newAppReportForZSQDao.getRegionManagerByUserId(noRepeatParentStr, entity.getSignDate(), condition);
                        for (int i = 0; i < userIdsList.size(); i++) {
                            String receiverId = userIdsList.get(i).getString("userId");
                            JSONObject obj = userDao.getViewJSONById(receiverId);
                            insertMessage(name, obj.getString("roleName"), entity.getCreateUserId(), entity.getSignDate(), receiverId);
                        }
                    }
                    return ((JSONObject) JSON.toJSON(entity));
                } else {
                    return null;
                }
            } else {
                //签退与末次拜访
                List<JSONObject> list = wReportCusVisitDao.getJSONListByCondition("AND CREATE_USER_ID = ?\n" +
                        "AND VISIT_MODE = '1'\n" +
                        "AND REPORT_DATE = ?\n" +
                        "order by CREATE_TIME DESC\n", new String[]{entity.getCreateUserId(), entity.getSignDate()});
                if (list != null && list.size() > 0) {
                    WReportCusVisitEntity visitEntity = JSON.toJavaObject(list.get(0), WReportCusVisitEntity.class);
                    //时间差
                    if (visitEntity.getLeaveTime() == null) {
                        //如果考勤签退后，再次面访签到会报错，所以加判断
                        return null;
                    }
                    long between = Math.abs(visitEntity.getLeaveTime().getTime() - entity.getSignOutTime().getTime());
                    String hour = new DecimalFormat("0.00").format(Double.parseDouble(String.valueOf(between)) / (60 * 60 * 1000));

                    String signOutTimeDiff = DateUtils.calculateTimeDifference(visitEntity.getLeaveTime(), entity.getSignOutTime());
                    //距离差
                    String signOutDistance = common.getDistanceByLatAndLng(
                            Double.parseDouble(visitEntity.getLeaveLatitude()),
                            Double.parseDouble(visitEntity.getLeaveLongitude()),
                            Double.parseDouble(entity.getSignOutLatitude()),
                            Double.parseDouble(entity.getSignOutLongitude()));
                    if (signOutDistance.contains("米")) {
                        signOutDistance = String.valueOf((Double.parseDouble(signOutDistance.replace("米", "")) / 1000));
                    } else {
                        signOutDistance = signOutDistance.replace("公里", "").replace("千米", "");
                    }
                    //平均速度
                    String signOutSpeed = new DecimalFormat("0.00").format(Double.parseDouble(signOutDistance) / Double.parseDouble((hour)));
                    if (between < 36000L) {
                        //如果操作签到与面访签到间隔小于36s，时间被除数为0，速度为∞，修改为0
                        signOutSpeed = "0";
                    }

                    entity.setSignOutDistance(signOutDistance + "公里"); //100公里
                    entity.setSignOutSpeed(signOutSpeed + "km/h"); //100Km/h
                    entity.setSignOutTimeDiff(signOutTimeDiff); // 1小时20分15秒
                    update(entity);
                    return ((JSONObject) JSON.toJSON(entity));
                } else {
                    return null;
                }
            }
        } else {
            return null;
        }
    }

    /**
     * 间隔/时间是否合理
     * <p>
     * 间隔>30min 或者 距离>15km 不合理
     *
     * @return
     */
    public boolean isInvalid(String signInDistance, long between) {
        long min = between / (60 * 1000);
        if (Double.parseDouble(signInDistance) > 15 || min > 30) {
            return true;
        }
        return false;
    }


    public void insertMessage(String name, String roleName, String createUserId, String date, String receiverId) {
        //保存消息到消息表t_sys_message
        MessageEntity messageEntity = new MessageEntity();
        messageEntity.setCreateUserId(createUserId);
        messageEntity.setCreateTime(userDao.getCurrentTime());
        messageEntity.setContent("首次面访");
        messageEntity.setMessageType(3);
        messageEntity.setDelFlag(0);
        switch (roleName) {
            case "区域经理":
                messageEntity.setOpenUrl("/reportForm/errorMessage/quYuFirstFaceApp.html?&userId=" + createUserId + "&dateTime=" + date + "&msg=0");
                break;
            case "区域组长":
            case "大区经理":
                messageEntity.setOpenUrl("/reportForm/errorMessage/daQuFirstFaceApp.html?&userId=" + createUserId + "&dateTime=" + date + "&msg=0");
                break;
            default://管理层
                messageEntity.setOpenUrl("/reportForm/errorMessage/managerFirstFaceApp.html?&userId=" + createUserId + "&dateTime=" + date + "&msg=0");
                break;
        }

        messageEntity.setOpenTitle(String.format(FIRST_VISIT_MESSAGE_ERROR, name));
        messageDao.add(messageEntity);
        //保存收件人表t_sys_message_receiver
        MessageReceiverEntity messageReceiverEntity = new MessageReceiverEntity();
        messageReceiverEntity.setMessageId(messageEntity.getId());
        messageReceiverEntity.setUserId(receiverId);
        messageReceiverEntity.setUserType(0);
        messageReceiverEntity.setReadFlag(0);
        messageReceiverDao.add(messageReceiverEntity);

        //发送企业微信
        JSONObject msgObject = new JSONObject();
        msgObject.put("userIds", receiverId);
        msgObject.put("content", String.format(FIRST_VISIT_MESSAGE_ERROR_WE, name,
                "http://oa.yingfeng365.com/page" + messageEntity.getOpenUrl() + "&receiverId=" + messageReceiverEntity.getId()));
        tokenDao.sendTextMsg(JSON.toJSONString(msgObject));

    }

    public JSONObject getSignInByDay(String userId, String signDate) {
        //查询当天签到信息
        Calendar signOutDate = Calendar.getInstance();
        boolean flag = DateUtils.isInDate(signOutDate.getTime(), "00:00:00", "03:00:00");
        if (flag) {
            signDate = DateUtils.getYesterday();
        }
        List<JSONObject> list = getJSONListByCondition(" AND USER_ID = ? AND SIGN_DATE = ? ", new Object[]{userId, signDate});
        return list.size() > 0 ? list.get(0) : null;
    }
}
